DELIMITER$$
    CREATE TRIGGER after_insert_affiliates
    AFTER INSERT ON PaymentTransactions FOR EACH ROW  
    
    BEGIN  
        
    END  

DELIMITER;

DECLARE INT @affiliate;
DECLARE @affiliateParentId INT;
SELECT  @affiliateId := AffiliateId AS temp, @affiliateParentId := AffiliateParentId, @userId := UserId FROM Affiliates WHERE AffiliateId=3;
SET @affiliateId := @userId+@affiliateParentId;
SELECT  @affiliateId 


DELIMITER $$
    DROP PROCEDURE IF EXISTS `testprint`$$
    
    CREATE PROCEDURE testprint()
    BEGIN
        
        SELECT  @affiliateParentId := AffiliateParentId FROM Affiliates WHERE AffiliateId=5;
        SET @commissionLevel = 5;
        WHILE @affiliateParentId IS NOT NULL DO
            UPDATE Affiliates SET AffiliateChildrenIds = @commissionLevel WHERE AffiliateId=@affiliateParentId;
            SELECT  @affiliateParentId := AffiliateParentId FROM Affiliates WHERE AffiliateId=@affiliateParentId;
            SET @commissionLevel := @commissionLevel+1;
        END WHILE;
        
    END$$
    
CALL testprint

SELECT AffPro.*, CommissionLevel, CommissionRate FROM (
SELECT Aff.*, RS, CPA, Lifetime, PromotionGroupId FROM ( 
SELECT AffiliateId, CreatedDate, AffiliateParentId, PromotionId FROM Affiliates WHERE AffiliateId=1) AS Aff
JOIN Promotions AS Pro ON Aff.PromotionId = Pro.PromotionId) AS AffPro
LEFT JOIN CommissionLevels AS Comle ON AffPro.PromotionGroupId = Comle.PromotionGroupId AND CommissionLevel =4


DELIMITER $$
    DROP PROCEDURE IF EXISTS `testprint`$$
    
    CREATE PROCEDURE testprint()
    BEGIN
	
	
        SELECT @affiliateId := Aff.AffiliateId, @createdDate := Aff.CreatedDate, @affiliateParentId := Aff.AffiliateParentId, @promotionId := Aff.PromotionId, @rs := RS, @cpa := CPA, @lifetime := Lifetime, @prmotionGroupId := PromotionGroupId 
        FROM (SELECT AffiliateId, CreatedDate, AffiliateParentId, PromotionId FROM Affiliates WHERE AffiliateId=1) AS Aff
        JOIN Promotions AS Pro ON Aff.PromotionId = Pro.PromotionId;
        
	
        IF DATEDIFF(NOW(),ADDDATE(@createdDate, @lifetime)) < 0 THEN		
            INSERT INTO AffiliateTransactions(PaymentTransactionId, LevelCommissionMoney, AffiliateId, AffiliateAncestorId, PromotionId, RS, CPA, CommissionLevel, CommissionRate)
            VALUES(NULL, NULL, @affiliateId, @affiliateParentId, @promotionId, @rs, @cpa, 0, NULL);
        END IF;
    END$$
    
CALL testprint